<?php
namespace common;


class batchInsert
{
    /**
     *批量插入的方法(只支持二维数组)
     *
     *  *[0] => Array
     * (
     * [wis_code] => WTG1231-8-20200510
     * [product_barcode] => G1231-UK-MMM210726001
     * [product_name] => 1.5米松木艺术画架+纸盒包装-英国
     * [reference_no] =>
     * [charge_date] => 2020-05-10
     * [putaway_date] => 2020-05-05 00:00:00
     * [length] => 119.00
     * [width] => 7.00
     * [height] => 6.00
     * [quantity] => 54
     * [volume] => 0.270
     * [bill_amount] => 0.000
     * [bill_currency_code] => GBP
     * [settlement_amount] => 0.000
     * [settlement_currency_code] => GBP
     * [day] => 5
     * [warehouse_code] => UK
     * )
     *
     * [1] => Array
     * (
     * [wis_code] => WTG1231-8-20200510
     * [product_barcode] => G1231-UK-UUU176019001
     * [product_name] => 镀铬双把冷热水龙头
     * [reference_no] =>
     * [charge_date] => 2020-05-10
     * [putaway_date] => 2020-05-05 00:00:00
     * [length] => 38.50
     * [width] => 21.00
     * [height] => 7.00
     * [quantity] => 55
     * [volume] => 0.330
     * [bill_amount] => 0.000
     * [bill_currency_code] => GBP
     * [settlement_amount] => 0.000
     * [settlement_currency_code] => GBP
     * [day] => 5
     * [warehouse_code] => UK
     * )
     * @param $table 表名
     * @param $data  数据
     * @return string
     *author :
     *createTime:
     */
    public static function insert($table, $data)
    {
        $insertSql = " insert into  `$table`   ";
        $insertDataSql = $lastInsertSql = "";
        foreach ($data as $key => $itemData) {
            if (0 == $key) {
                $insertSql .= "(";
                $lastInsertSql = " ON DUPLICATE KEY UPDATE  ";
                $num = 0;
                foreach ($itemData as $newKey => $newItem) {
                    $num++;
                    $insertSql .= "`" . $newKey . "`";
                    $lastInsertSql .= $newKey . "=" . " VALUES(" . $newKey . ")";
                    if (count($itemData) > $num) {
                        $insertSql .= ",";
                        $lastInsertSql .= ",";
                    }
                }
                $insertSql .= ")  values ";
            }
            $num = 0;
            foreach ($itemData as $newKey => $newItem) {
                $num == 0 && $insertDataSql .= "(";
                $insertDataSql .= "'" . addslashes($newItem) . "'";
                if (count($itemData) - 1 == $num) {
                    $insertDataSql .= ")";
                    count($data) - 1 != $key && $insertDataSql .= ",";
                } else {
                    $insertDataSql .= ",";
                }
                $num++;
            }
        }
        return $insertSql . $insertDataSql . $lastInsertSql;
    }



    /**
     * MYSQL更新操作
     * @param array $array
     * @param string $table
     * @param string|null $where
     * @return number|boolean
     */
    public static function update(array $array, string $table, string $where = null)
    {
        $sets = '';
        foreach ($array as $key => $val) {
            $sets .= $key . "='" . $val . "',";
        }
        $sets = rtrim($sets, ','); //去掉SQL里的最后一个逗号
        $where = $where == null ? '' : ' WHERE ' . $where;
        return "UPDATE {$table} SET {$sets} {$where}";
    }


    //插入sql语句
    public static function insertSql($table, $data)
    {
        $keys = array_keys($data);//获取所有字段名
        $keys = array_map(function ($key) {//在所有的字段名上添加反引号
            return "`{$key}`";
        }, $keys);
        $keys = implode(',', $keys);

        $values = array_values($data);//获取所有的值
        $values = array_map(function ($value) {//所有的值上添加单引号
            return "'{$value}'";
        }, $values);
        $values = implode(',', $values);//值通过逗号连接起来
        return "INSERT INTO `{$table}` ($keys) VALUES ($values)";
    }

    public static function buildInsertQuery($tableName, $data) {
        if (empty($data)) {
            return '';
        }
        $keys = implode(',', array_keys($data[0]));
        $values = [];
        foreach ($data as $row) {
            $rowValues = array_map(function($value) {
                return "'".addslashes($value)."'";
            }, array_values($row));
            $values[] = '('.implode(',', $rowValues).')';
        }
        $updateValues = array_map(function($key, $value) {
            return "$key=VALUES($key)";
        }, array_keys($data[0]), array_values($data[0]));
        return "INSERT INTO $tableName ($keys) VALUES ".implode(',', $values)." ON DUPLICATE KEY UPDATE ".implode(',', $updateValues);
    }
}